<?php

namespace Kateba\GOOBundle\Command;

use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

class CreateViewCommand extends ContainerAwareCommand {

    protected function configure() {
        $this->setName('create:view')
                ->setDescription('create the database view');
    }

    protected function execute(InputInterface $input, OutputInterface $output) {
        $this->getContainer()->get('doctrine')->getEntityManager()->getConnection()->exec("
            drop table if exists `search`;
            create or replace view `search` as
            SELECT id, 'call' as type, NULL as scope, name, description, website, image, funding_agency_id, study_field_id, mobility_level_id, country_id, NULL as project_type, created_at FROM `call`
            union
            select id, 'project' as type, scope, name, description, website, image, funding_agency_id, study_field_id, NULL as mobility_level_id, NULL as country_id, type as project_type, created_at FROM `project`;
        ");
    }

}